if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCOIUserHierarchy]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCOIUserHierarchy]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/***************************************************************                            
* Name:   [GetCOIUserHierarchy]                            
* Purpose:  Retrieves COIIds matching the filter parameters passed in using multiple table joins                             
* @admPersonId      Domain User ID (REQUIRED)                                      
* @firstName      FirstName of the user.                                    
* @lastName       LastName for the user.
* @supervisorFirstName  Supervisor firstName                               
* @supervisorLastName  Supervisor last name                                    
* @userName       UserName for the user.                                    
* @isPolicyQuestionSelected   Determines wheteher or not the result should bring back only COIs with policy questions.                                    
* @isNothingToDiscloseSelected Determines wheteher or not the result should bring back only COIs with Nothing to disclose.                                    
* @statusXml     XML string containing the selected status.                                
* @recursive      determines whether to bring back the entire descendants or the direct descendants                              
* @SortColumn  Column to sort on                                 
* @sortOrder   Determines whether or not the sort should be performed in ascending or descending order.          
* @hasExistingRelation  Determines whether or not to bring the inactive relation                   
*********************************************************************/                            
CREATE PROCEDURE [dbo].[GetCOIUserHierarchy]                                
(                                
@admPersonId int,                                
@firstName  VARCHAR(64)=null,                                
@lastName VARCHAR(64)=null,
@supervisorFirstName  VARCHAR(64)=null,                               
@supervisorLastName  VARCHAR(64)=null,   
@userName VARCHAR(36)=null,                                
@isPolicyQuestionSelected BIT=0,                                
@isNothingToDiscloseSelected BIT=0,                                
@statusXml text=null,                                 
@recursive BIT= 1,                        
@sortColumn varchar(150)= 'LastName',                        
@sortOrder bit=0, -->Asc,1-->Desc              
@hasExistingRelation bit=0,
@HierarchyTypeId int = 0                              
)                                
AS BEGIN                                
                                
SET NOCOUNT ON                                
                                
if(@firstName is null)                                
 Set @firstName='%'                                
else                                
 Set @firstName=@firstName+'%'                                 
                                
if(@lastName is null)                                
 Set @lastName='%'                                
else                                
 Set @lastName=@lastName+'%' 

if(@supervisorFirstName is null)                                
 Set @supervisorFirstName='%'                                
else                                
 Set @supervisorFirstName=@supervisorFirstName+'%'                                 
                                
if(@supervisorLastName is null)                                
 Set @supervisorLastName='%'                                
else                                
 Set @supervisorLastName=@supervisorLastName+'%'                                
                                
if(@userName is null)                                
 Set @userName='%'                                
else                                
 Set @userName=@userName+'%'                         
        
if  @sortColumn ='Name'                                
Set @sortColumn='LastName'      
--Logic for recursion based on the Hierarchy Type.
IF (@HierarchyTypeId IN (1,2))--Board,Committee
BEGIN
	SET @recursive = 0
END
--Delcaring a temp table to store the results of the Main Query's
--Later on Distinct rows of this temp table are retuned.
DECLARE @admPersonIds TABLE (SEQUENCE INT IDENTITY, [ID] INT,COIId INT, SupervisorId INT)        

      
   IF (@statusXml is null)                                 
      BEGIN                            
       IF  @sortColumn='Supervisor.LastName'or @sortColumn = 'LastName'  or @sortColumn=null    OR @sortColumn = 'Status'                      
		  BEGIN                        
			  INSERT INTO @admPersonIds                                
			  SELECT adp.[Id],coi.[Id]as COIId,adp1.[Id] as SupervisorId            
			  FROM  dbo.[GetDescendants](@admPersonId, @recursive,@hasExistingRelation,@HierarchyTypeId)  sou                                  
			   left JOIN AdmPerson adp                                
				on adp.[Id]=sou.[ChildId]   -->Person-->Child                               
			   LEFT outer JOIN  ConflictOfInterest coi                                
				on adp.[id]=coi.[AdmPersonId]                        
			  AND Isnull(cast(coi.[Id] as varchar(10)),'*') =(Select isnull(cast(Max(Id)as varchar(10)),'%') as LatestCOIId from conflictofinterest where admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)            
			   LEFT outer JOIN  FWKDomainUSer fdu                                
				on adp.[fwkDomainUserId]=fdu.[Id] 
			  Left Outer Join AdmPerson adp1                         
			  on adp1.[Id]= sou.[ParentId]                                
	                                           
			  WHERE                                 
				ISNULL(adp.[firstName], '*') like @firstName                                
				AND                                
				ISNULL(adp.[lastName], '*') like @lastName                                
				AND                                
				ISNULL(fdu.[userName], '*') like @userName
				AND                                
				ISNULL(adp1.[lastName], '*') like @supervisorLastName                                
				AND                                
				ISNULL(adp1.[firstName], '*') like @supervisorFirstName                                 
				AND                                
				(ISNULL(cast(coi.[HasOutsideActivities] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
						 When 0 then '%'         
						 else                           
						 '0'                          
						 END                 
				and  ISNULL(cast(coi.[HasEquityInterests] as varchar(1)),'*')like Case @isNothingToDiscloseSelected                                  
						 When 0 then '%'                          
						 else                           
						 '0'                          
						 END                 
				and ISNULL(cast(coi.[HasFamilyAssociations] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
						 When 0 then '%'                         
						 else                           
						 '0'                          
						 END   
				and ISNULL(cast(coi.[Royalty] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
						 When 0 then '%'                         
						 else                           
						 '0'                          
						 END)                        
				AND ISNULL(cast(coi.[PolicyViolation] as varchar(1)),'*') Like Case @isPolicyQuestionSelected                           
						When 0 then '%'                          
						 else                           
						 '1'                          
						 END                             
				ORDER BY                          
				CASE @sortOrder                        
					WHEN 0 THEN                         
						CASE @sortColumn                        
							WHEN 'Supervisor.LastName' THEN adp1.[lastName]            
							WHEN 'LastName' THEN adp.[lastName]   
							WHEN 'Status'  THEN coi.Status                                                                    
							ELSE adp.[lastName]                       
						END                        
				END                                     
				DESC,                        
				CASE @sortOrder                        
					WHEN 1 THEN                         
						CASE @sortColumn                        
							WHEN 'Supervisor.LastName' THEN adp1.[lastName]            
							WHEN 'LastName' THEN adp.[lastName]      
							 WHEN 'Status'  THEN coi.Status                      
							ELSE adp.[lastName]                                          
						END                        
				END           
			  , CASE @sortOrder                        
					WHEN 0 THEN                         
				CASE @sortColumn                        
					WHEN 'Supervisor.LastName' THEN adp1.[FirstName]            
					WHEN 'LastName' THEN adp.[FirstName]                                                                 
					ELSE adp.[FirstName]                       
					END                        
				END                                     
				DESC,                        
				CASE @sortOrder                        
					WHEN 1 THEN                         
						CASE @sortColumn                        
							WHEN 'Supervisor.LastName' THEN adp1.[FirstName]            
							WHEN 'LastName' THEN adp.[FirstName]                      
							ELSE adp.[FirstName]                                          
						END                        
				END                                 
			END                      
          Else If(@sortColumn='PolicyViolation' OR @sortColumn='EquityInterest' or @sortColumn = 'FamilyAssociation'or @sortColumn = 'OutsideActivity' or @sortColumn = 'OtherActivity'or @sortColumn = 'Royalty')                        
                                 
        BEGIN       
			INSERT INTO @admPersonIds                 
            SELECT adp.[Id],coi.[Id]as COIId, adp1.[Id] as SupervisorId                        
             FROM  dbo.[GetDescendants](@admPersonId, @recursive,@hasExistingRelation,@HierarchyTypeId)  sou                                   
              Left JOIN AdmPerson adp                                
				on adp.[Id]=sou.[ChildId]                                  
              LEFT outer JOIN  ConflictOfInterest coi                                
               on adp.[id]=coi.[AdmPersonId]                        
              AND Isnull(cast(coi.[Id] as varchar(10)),'*')= (Select isnull(cast(Max(Id)as varchar(10)),'%') as LatestCOIId from conflictofinterest where admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)            
              LEFT outer JOIN  FWKDomainUSer fdu                                
               on adp.[fwkDomainUserId]=fdu.[Id]            
			  Left Outer Join AdmPerson adp1                         
			   on adp1.[Id]= sou.[ParentId]                                 
            
             WHERE                                 
               ISNULL(adp.[firstName], '*') like @firstName                                
               AND                                
               ISNULL(adp.[lastName], '*') like @lastName                                
               AND
               ISNULL(fdu.[userName], '*') like @userName 
               AND                                
				ISNULL(adp1.[lastName], '*') like @supervisorLastName                                
				AND                                
				ISNULL(adp1.[firstName], '*') like @supervisorFirstName                 
                AND                                
				   (ISNULL(cast(coi.[HasOutsideActivities] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
				   When 0 then '%'                          
				   else                           
				   '0'                          
				   END                 
				and  ISNULL(cast(coi.[HasEquityInterests] as varchar(1)),'*')like Case @isNothingToDiscloseSelected                                  
				   When 0 then '%'                          
				   else                           
				   '0'                          
				   END                 
				and ISNULL(cast(coi.[HasFamilyAssociations] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
				   When 0 then '%'                          
				   else                           
				   '0'                          
				   END   
				and ISNULL(cast(coi.[Royalty] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
				   When 0 then '%'                         
				   else                           
				   '0'                          
				   END)                        
				AND                                  
					ISNULL(cast(coi.[PolicyViolation] as varchar(1)),'*') Like Case @isPolicyQuestionSelected                           
					 When 0 then '%'                          
					 else                           
					 '1'                          
					 END                            
               ORDER BY                          
               CASE @sortOrder                        
                 WHEN 1 THEN                         
					  CASE @sortColumn                        
					   WHEN 'PolicyViolation' THEN coi.PolicyViolation                        
					   WHEN 'EquityInterest' THEN coi.HasEquityInterests                    
					   WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                    
					   WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                    
					   WHEN 'OtherActivity' THEN coi.OtherActivity   
					   WHEN 'Royalty' THEN coi.Royalty                         
					   ELSE coi.PolicyViolation                        
					   END                        
               END                                     
               DESC,                        
               CASE @sortOrder                        
                 WHEN 0 THEN                         
                  CASE @sortColumn                        
					   WHEN 'PolicyViolation' THEN coi.PolicyViolation                        
					   WHEN 'EquityInterest' THEN coi.HasEquityInterests                                                            
					   WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                        
					   WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                                                            
					   WHEN 'OtherActivity' THEN coi.OtherActivity    
					   WHEN 'Royalty' THEN coi.Royalty                            
					   ELSE coi.PolicyViolation                        
                  END                        
               END                                         
                        
         END                        
                         
       Else If(@sortColumn='PolicyViolation' OR @sortColumn='EquityInterest' or @sortColumn = 'FamilyAssociation'or @sortColumn = 'OutsideActivity' or @sortColumn = 'Royalty')                        
                                 
        BEGIN                        
			INSERT INTO @admPersonIds
            SELECT adp.[Id],coi.[Id]as COIId , adp1.[Id] as SupervisorId                        
             FROM  dbo.[GetDescendants](@admPersonId, @recursive,@hasExistingRelation,@HierarchyTypeId)  sou                                   
              Left JOIN AdmPerson adp                                
               on adp.[Id]=sou.[ChildId]                           
              LEFT outer JOIN  ConflictOfInterest coi                                
               on adp.[id]=coi.[AdmPersonId] 
			  AND Isnull(cast(coi.[Id] as varchar(10)),'*')= (Select isnull(cast(Max(Id)as varchar(10)),'%') as LatestCOIId from conflictofinterest where admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)            
              LEFT outer JOIN  FWKDomainUSer fdu                                
               on adp.[fwkDomainUserId]=fdu.[Id]                                
               Left Outer Join AdmPerson adp1                         
			   on adp1.[Id]= sou.[ParentId]             
                                
             WHERE                                 
                ISNULL(adp.[firstName], '*') like @firstName                                
                AND                                
                ISNULL(adp.[lastName], '*') like @lastName                                
                AND                                
                ISNULL(fdu.[userName], '*') like @userName                                
                AND                                
				ISNULL(adp1.[lastName], '*') like @supervisorLastName                                
				AND                                
				ISNULL(adp1.[firstName], '*') like @supervisorFirstName  
				AND                               
               (ISNULL(cast(coi.[HasOutsideActivities] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END                 
				and  ISNULL(cast(coi.[HasEquityInterests] as varchar(1)),'*')like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END             
				and ISNULL(cast(coi.[HasFamilyAssociations] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'             
                     else                           
                     '0'                          
                     END  
				and ISNULL(cast(coi.[Royalty] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                         
                     else                           
                     '0'                          
                     END )                        
				AND ISNULL(cast(coi.[PolicyViolation] as varchar(1)),'*') Like Case @isPolicyQuestionSelected                           
                     When 0 then '%'                          
                     else                           
                     '1'                          
                     END                            
               ORDER BY                          
               CASE @sortOrder                        
					WHEN 1 THEN                         
						CASE @sortColumn                        
						   WHEN 'PolicyViolation' THEN coi.PolicyViolation                        
						   WHEN 'EquityInterest' THEN coi.HasEquityInterests                    
						   WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                    
						   WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                    
						   WHEN 'OtherActivity' THEN coi.OtherActivity  
						   WHEN 'Royalty' THEN coi.Royalty                    
							ELSE coi.PolicyViolation                        
						END                        
               END                                     
               DESC,                        
			   CASE @sortOrder                        
						 WHEN 0 THEN                         
						  CASE @sortColumn                        
						   WHEN 'PolicyViolation' THEN coi.PolicyViolation                 
						   WHEN 'EquityInterest' THEN coi.HasEquityInterests                                                            
						   WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                        
						   WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                                                            
						   WHEN 'Royalty' THEN coi.Royalty                           
						   ELSE coi.PolicyViolation                        
						  END          
               END                                     
                        
         END                        
        Else If(@sortColumn='LastNotified' OR @sortColumn='LastSubmitted' or @sortColumn = 'DateReviewed')                        
                           
         BEGIN                        
           INSERT INTO @admPersonIds
           SELECT adp.[Id],coi.[Id]as COIId, adp1.[Id] as SupervisorId                         
            FROM  dbo.[GetDescendants](@admPersonId, @recursive,@hasExistingRelation,@HierarchyTypeId)  sou                                   
             Left JOIN AdmPerson adp                                
              on adp.[Id]=sou.[ChildId]                                 
             LEFT outer JOIN  ConflictOfInterest coi                                
              on adp.[id]=coi.[AdmPersonId]                        
             AND Isnull(cast(coi.[Id] as varchar(10)),'*')= (Select isnull(cast(Max(Id)as varchar(10)),'%') as LatestCOIId from conflictofinterest where admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)            
             LEFT outer JOIN  FWKDomainUSer fdu                                
              on adp.[fwkDomainUserId]=fdu.[Id]                                
              Left Outer Join AdmPerson adp1                         
			  on adp1.[Id]= sou.[ParentId]                                
            WHERE                 
              ISNULL(adp.[firstName], '*') like @firstName                                
              AND                                
              ISNULL(adp.[lastName], '*') like @lastName                                
              AND                                
              ISNULL(fdu.[userName], '*') like @userName
			  AND                                
			  ISNULL(adp1.[lastName], '*') like @supervisorLastName                                
			  AND                                
              ISNULL(adp1.[firstName], '*') like @supervisorFirstName                                 
              AND                        
              (ISNULL(cast(coi.[HasOutsideActivities] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END                 
			  and  ISNULL(cast(coi.[HasEquityInterests] as varchar(1)),'*')like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END                 
			  and ISNULL(cast(coi.[HasFamilyAssociations] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END   
			  and ISNULL(cast(coi.[Royalty] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                         
                     else                           
                     '0'                          
                     END)                      
            AND                                  
            ISNULL(cast(coi.[PolicyViolation] as varchar(1)),'*') Like Case @isPolicyQuestionSelected                           
                     When 0 then '%'                          
                     else                           
                     '1'                          
                     END                             
              ORDER BY                          
              CASE @sortOrder                        
					WHEN 0 THEN                         
						 CASE @sortColumn                        
						  WHEN 'LastNotified' THEN coi.LastNotified                        
						  WHEN 'LastSubmitted' THEN coi.DateSubmitted                                       
						  WHEN 'DateReviewed' THEN coi.DateReviewed                        
						  ELSE coi.DateSubmitted                        
						  END                        
					END                                     
              DESC,                        
              CASE @sortOrder                        
                WHEN 1 THEN                         
						CASE @sortColumn                        
						  WHEN 'LastNotified' THEN coi.LastNotified                        
						  WHEN 'LastSubmitted' THEN coi.DateSubmitted                                                            
						  WHEN 'DateReviewed' THEN coi.DateReviewed                        
						  ELSE coi.DateSubmitted                        
						END                        
				END                        
                                     
			END                        
		END                                          
    ELSE                                
      BEGIN                         
       declare @xmlDoc int                                  
        declare @statusTable table (Sequence int identity, Status varchar(75))                                  
        exec sp_xml_preparedocument @xmlDoc output, @statusXml                            
        insert into @statusTable select [Col1] from openxml(@xmlDoc, '//Table1',2) with (Col1 varchar(75))                           
        exec sp_xml_removedocument @xmlDoc                          
                          
         IF @sortColumn='Supervisor.LastName' or @sortColumn='LastName'     OR @sortColumn = 'Status'                   
           BEGIN                           
              INSERT INTO @admPersonIds
              SELECT adp.[Id],coi.[Id]as COIId, adp1.[Id] as SupervisorId                         
             FROM  dbo.[GetDescendants](@admPersonId, @recursive,@hasExistingRelation,@HierarchyTypeId)  sou                                   
              Left JOIN AdmPerson adp                                
               on adp.[Id]=sou.[ChildId]                                 
               LEFT JOIN  ConflictOfInterest coi                                
             on adp.[id]=coi.[AdmPersonId]
			 AND Isnull(cast(coi.[Id] as varchar(10)),'*')= (Select isnull(cast(Max(Id)as varchar(10)),'%') as LatestCOIId from conflictofinterest where admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)            
               LEFT JOIN  FWKDomainUSer fdu                                
             on adp.[fwkDomainUserId]=fdu.[Id]                          
               INNER JOIN @statusTable sTab                          
             on sTab.[Status]=coi.[Status]                        
              Left Outer Join AdmPerson adp1                         
             on adp1.[Id]= sou.[ParentId]                         
                                              
			WHERE                                 
             ISNULL(adp.[firstName], '*') like @firstName                                
             AND                                
             ISNULL(adp.[lastName], '*') like @lastName                                
             AND                                
             ISNULL(fdu.[userName], '*') like @userName
			 AND                                
             ISNULL(adp1.[lastName], '*') like @supervisorLastName                                
             AND                                
             ISNULL(adp1.[firstName], '*') like @supervisorFirstName                                 
             AND                                
               (ISNULL(cast(coi.[HasOutsideActivities] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END                 
				and  ISNULL(cast(coi.[HasEquityInterests] as varchar(1)),'*')like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else  '0'                          
                     END                 
				and ISNULL(cast(coi.[HasFamilyAssociations] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END   
				and ISNULL(cast(coi.[Royalty] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                         
                     else                           
                     '0'                          
                     END)                        
				AND                                  
					ISNULL(cast(coi.[PolicyViolation] as varchar(1)),'*') Like Case @isPolicyQuestionSelected                           
                     When 0 then '%'                          
                     else                           
                     '1'                          
                     END            
                                            
              ORDER BY                          
               CASE @sortOrder                        
					WHEN 1 THEN                         
						CASE @sortColumn                        
							WHEN 'Supervisor.LastName' THEN adp1.[lastName]                                          
							WHEN 'LastName' THEN adp.[lastName] 
							WHEN 'Status'  THEN coi.Status                                               
							ELSE adp.[LastName]                                  
						END                        
				END                                     
               DESC,                        
               CASE @sortOrder                        
                 WHEN 0 THEN                         
                  CASE @sortColumn                        
                   WHEN 'Supervisor.LastName' THEN adp1.[lastName]                                          
                    WHEN 'LastName' THEN adp.[lastName]         
					WHEN 'Status'  THEN coi.Status                                       
                   ELSE adp.[LastName]                                     
                  END                        
               END           
			 ,CASE @sortOrder                        
				  WHEN 0 THEN                         
						CASE @sortColumn                        
						 WHEN 'Supervisor.LastName' THEN adp1.[FirstName]            
						 WHEN 'LastName' THEN adp.[FirstName]                                                                 
						 ELSE adp.[FirstName]                       
						END                        
			 END                                     
			DESC,                        
			CASE @sortOrder                        
				 WHEN 1 THEN                         
				  CASE @sortColumn                        
				  WHEN 'Supervisor.LastName' THEN adp1.[FirstName]            
				  WHEN 'LastName' THEN adp.[FirstName]                      
				  ELSE adp.[FirstName]                                          
				END                        
			END                                       
           END                    
         Else If(@sortColumn='PolicyViolation' OR @sortColumn='EquityInterest' or @sortColumn = 'FamilyAssociation'or @sortColumn = 'OutsideActivity' or @sortColumn = 'OtherActivity'or @sortColumn = 'Royalty')                        
                                 
            BEGIN                        
             INSERT INTO @admPersonIds
             SELECT adp.[Id],coi.[Id]as COIId , adp1.[Id] as SupervisorId                        
             FROM  dbo.[GetDescendants](@admPersonId, @recursive,@hasExistingRelation,@HierarchyTypeId)  sou                                   
              Left JOIN AdmPerson adp                                
               on adp.[Id]=sou.[ChildId]                                 
               LEFT JOIN  ConflictOfInterest coi                                
             on adp.[id]=coi.[AdmPersonId]                        
             AND Isnull(cast(coi.[Id] as varchar(10)),'*')= (Select isnull(cast(Max(Id)as varchar(10)),'%') as LatestCOIId from conflictofinterest where admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)            
               LEFT JOIN  FWKDomainUSer fdu                                
             on adp.[fwkDomainUserId]=fdu.[Id]                          
               INNER JOIN @statusTable sTab                          
             on sTab.[Status]=coi.[Status]                   
               Left Outer Join AdmPerson adp1                         
			 on adp1.[Id]= sou.[ParentId]                  
               WHERE                                 
             ISNULL(adp.[firstName], '*') like @firstName                                
             AND                                
             ISNULL(adp.[lastName], '*') like @lastName                                
             AND                    
             ISNULL(fdu.[userName], '*') like @userName
			 AND                                
             ISNULL(adp1.[lastName], '*') like @supervisorLastName                                
             AND                                
             ISNULL(adp1.[firstName], '*') like @supervisorFirstName                                 
             AND                                
               (ISNULL(cast(coi.[HasOutsideActivities] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END         
			 and  ISNULL(cast(coi.[HasEquityInterests] as varchar(1)),'*')like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END                 
             and ISNULL(cast(coi.[HasFamilyAssociations] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END   
			 and ISNULL(cast(coi.[Royalty] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                         
                     else                           
                     '0'                          
                     END)                      
            AND  ISNULL(cast(coi.[PolicyViolation] as varchar(1)),'*') Like Case @isPolicyQuestionSelected                           
                     When 0 then '%'                          
                     else                           
							'1'                          
                     END                                    
                                            
              ORDER BY                          
               CASE @sortOrder                        
                 WHEN 1 THEN                         
                  CASE @sortColumn                        
                   WHEN 'PolicyViolation' THEN coi.PolicyViolation                        
                   WHEN 'EquityInterest' THEN coi.HasEquityInterests                    
                   WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                    
                   WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                    
                   WHEN 'OtherActivity' THEN coi.OtherActivity  
				   WHEN 'Royalty' THEN coi.Royalty                                                            
                   ELSE coi.PolicyViolation                        
                   END                        
               END                                     
               DESC,                        
               CASE @sortOrder                        
                 WHEN 0 THEN                         
                  CASE @sortColumn                        
                   WHEN 'PolicyViolation' THEN coi.PolicyViolation                        
                   WHEN 'EquityInterest' THEN coi.HasEquityInterests                                                            
                   WHEN 'FamilyAssociation' THEN coi.HasFamilyAssociations                        
				   WHEN 'OutsideActivity' THEN coi.HasOutsideActivities                        
                   WHEN 'OtherActivity' THEN coi.OtherActivity  
				   WHEN 'Royalty' THEN coi.Royalty                                                             
                   ELSE coi.PolicyViolation                        
                  END                        
               END                                      
            END                        
         Else If(@sortColumn='LastNotified' OR @sortColumn='LastSubmitted' or @sortColumn = 'DateReviewed')                        
                             
            BEGIN                        
             INSERT INTO @admPersonIds
             SELECT adp.[Id],coi.[Id]as COIId , adp1.[Id] as SupervisorId                        
             FROM  dbo.[GetDescendants](@admPersonId, @recursive,@hasExistingRelation,@HierarchyTypeId)  sou                                   
              Left JOIN AdmPerson adp                                
               on adp.[Id]=sou.[ChildId]                           
               LEFT JOIN  ConflictOfInterest coi                                
             on adp.[id]=coi.[AdmPersonId]                        
             AND Isnull(cast(coi.[Id] as varchar(10)),'*')= (Select isnull(cast(Max(Id)as varchar(10)),'%') as LatestCOIId from conflictofinterest where admPersonId=adp.[Id] and Status <> 'COISTATUS_TERMINATED' AND coi.COITypeId = 1)            
               LEFT JOIN  FWKDomainUSer fdu                                
             on adp.[fwkDomainUserId]=fdu.[Id]                          
               INNER JOIN @statusTable sTab                          
             on sTab.[Status]=coi.[Status]                        
              Left Outer Join AdmPerson adp1                         
          on adp1.[Id]= sou.[ParentId]                                               
               WHERE                                 
             ISNULL(adp.[firstName], '*') like @firstName                                
             AND                                
             ISNULL(adp.[lastName], '*') like @lastName                                
             AND                                
             ISNULL(fdu.[userName], '*') like @userName
			 AND                                
             ISNULL(adp1.[lastName], '*') like @supervisorLastName                                
             AND                                
             ISNULL(adp1.[firstName], '*') like @supervisorFirstName                                 
             AND                                
              (ISNULL(cast(coi.[HasOutsideActivities] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
					When 0 then '%'                          
                     else                           
                     '0'                          
                     END                 
			and  ISNULL(cast(coi.[HasEquityInterests] as varchar(1)),'*')like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END                 
			and ISNULL(cast(coi.[HasFamilyAssociations] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                          
                     else                           
                     '0'                          
                     END   
			and ISNULL(cast(coi.[Royalty] as varchar(1)),'*') like Case @isNothingToDiscloseSelected                                  
                     When 0 then '%'                         
					else                           
                     '0'                          
                     END)                        
            AND ISNULL(cast(coi.[PolicyViolation] as varchar(1)),'*') Like Case @isPolicyQuestionSelected                           
                     When 0 then '%'                          
                     else                           
                     '1'                          
                     END                                   
                                            
              ORDER BY                          
              CASE @sortOrder                        
                WHEN 0 THEN                         
                 CASE @sortColumn                        
                  WHEN 'LastNotified' THEN coi.LastNotified                        
                  WHEN 'LastSubmitted' THEN coi.DateSubmitted                                                            
                  WHEN 'DateReviewed' THEN coi.DateReviewed                        
                  ELSE coi.DateSubmitted                        
                  END                        
              END                                     
			DESC,                        
              CASE @sortOrder                        
                WHEN 1 THEN                         
                 CASE @sortColumn                        
                  WHEN 'LastNotified' THEN coi.LastNotified                        
                  WHEN 'LastSubmitted' THEN coi.DateSubmitted                                                            
                  WHEN 'DateReviewed' THEN coi.DateReviewed                        
                  ELSE coi.DateSubmitted           
                 END                        
              END                        
          END                         
      END   
      
           
      
      SELECT DISTINCT Id,COIId,SupervisorId FROM @admPersonIds                     
    END     